package com.rensanning.poi;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class T1_Workbook extends AbstractSample {

	private static String FILE_DIR = "C:\\Users\\Administrator\\Desktop\\";

	private static final String TEST_WORKBOOK_NAME = FILE_DIR + "sample_workbook.xls";
	private static final String TEST_WORKBOOK_NAME2 = FILE_DIR + "sample_workbook.xlsx";

	private static final String TEST_WORKBOOK_NAME_ENCRYPTED = "resource/sample_workbook_encrypted.xls";
	private static final String TEST_WORKBOOK_PASSWORD = "pass@rensanning";

	public static void main(String[] args) {
		
		File f = new File(FILE_DIR);
		if (!f.isDirectory()) {
			f.mkdirs();
		}
		
		T1_Workbook t = new T1_Workbook();

		//生成Workbook
		t.createWorkbook();
		
		//生成Workbook OOXML形式(.xlsx)
		//t.createWorkbookOOXMl();

		//打开Workbook
		//t.openWorkbook();

		//打开加密的Workbook(读加密)
		//t.openEncryptedWorkbook();

		//追加Sheet
		//t.appendSheet();
		
		//复制Sheet
		//t.copySheet();

		//修改Sheet名称
		//t.setSheetName();

		//删除Sheet
		//t.deleteSheet();
		
		//设置下部Sheet名的Tab的第一个可见Tab
		//t.setFirstSheet();
		
		//调整Sheet顺序
		//t.orderSheet();
		
		//设置当前Sheet
		//t.setActiveSheet();
		
		//固定窗口
		//t.freezeWindow();
				
		//分割窗口
		//t.aplitWindow();
		
	}

	public void createWorkbook() {

		//生成Workbook
		HSSFWorkbook wb = new HSSFWorkbook();

		//添加Worksheet（不添加sheet时生成的xls文件打开时会报错）
		@SuppressWarnings("unused")
		Sheet sheet1 = wb.createSheet();
		@SuppressWarnings("unused")
		Sheet sheet2 = wb.createSheet();
		@SuppressWarnings("unused")
		Sheet sheet3 = wb.createSheet("new sheet");
		@SuppressWarnings("unused")
		Sheet sheet4 = wb.createSheet("rensanning");
		
		createExcelFile(wb, TEST_WORKBOOK_NAME);		
	}
	
	public void createWorkbookOOXMl() {

		//生成Workbook
		XSSFWorkbook wb = new XSSFWorkbook();

		@SuppressWarnings("unused")
		Sheet sheet1 = wb.createSheet();
		@SuppressWarnings("unused")
		Sheet sheet2 = wb.createSheet();
		@SuppressWarnings("unused")
		Sheet sheet3 = wb.createSheet("new sheet");
		@SuppressWarnings("unused")
		Sheet sheet4 = wb.createSheet("rensanning");
		
		createExcelFile(wb, TEST_WORKBOOK_NAME2);
	}

	public void openWorkbook() {
		FileInputStream in = null;
		Workbook wb = null;

		try {
			in = new FileInputStream(TEST_WORKBOOK_NAME);
			wb = WorkbookFactory.create(in);
		} catch (IOException e) {
			System.out.println(e.toString());
		} catch (InvalidFormatException e) {
			System.out.println(e.toString());
		} finally {
			try {
				in.close();
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}

		System.out.println("====================Workbook====================");
		System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
		System.out.println("Sheet3's name:" + wb.getSheetName(3));
		System.out.println();

		try {
			in = new FileInputStream(TEST_WORKBOOK_NAME);
			POIFSFileSystem fs = new POIFSFileSystem(in);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			System.out.println(e.toString());
		} finally {
			try {
				in.close();
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}

		System.out.println("====================Workbook====================");
		System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
		System.out.println("Sheet3's name:" + wb.getSheetName(3));
		System.out.println();
	}
	
	public void openEncryptedWorkbook() {
		FileInputStream input = null;
		BufferedInputStream binput = null;
		POIFSFileSystem poifs = null;

		try {
			input = new FileInputStream(TEST_WORKBOOK_NAME_ENCRYPTED);
			binput = new BufferedInputStream(input);
			poifs = new POIFSFileSystem(binput);

			Biff8EncryptionKey.setCurrentUserPassword(TEST_WORKBOOK_PASSWORD);

			HSSFWorkbook wb = new HSSFWorkbook(poifs);

			System.out.println("====================EncryptedWorkbook====================");
			System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
			System.out.println("Sheet0's name:" + wb.getSheetName(0));
			System.out.println();

		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				binput.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public void appendSheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);
		
		@SuppressWarnings("unused")
		Sheet sheet = wb.createSheet("append sheet");
		
		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}

	public void copySheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

	    wb.cloneSheet(1);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	public void setSheetName() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

	    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
	    	wb.setSheetName(i, "SheetName"+(i+1));
	    }

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	public void deleteSheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

		wb.removeSheetAt(1);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	public void setActiveSheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

		//设置当前Sheet
		wb.setActiveSheet(wb.getNumberOfSheets() - 1);
		//（Excel的当前Sheet被设置，需要结合setSelected使用，不然下部Sheet名的Tab还是默认为第一个）
		//（需要选择多个Sheet的话，每个Sheet调用setSelected(true)即可）
		wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	public void setFirstSheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

		//设置下部Sheet名的Tab的第一个可见Tab（以左的Sheet看不见）
		wb.setFirstVisibleTab(2);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	public void orderSheet() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

	    wb.setSheetOrder("rensanning", 1);
	    wb.setSheetOrder(wb.getSheetName(2), 0);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
	
	public void freezeWindow() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

		wb.getSheet("Sheet0").createFreezePane(2, 2);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}

	public void aplitWindow() {
		
		Workbook wb = readExcelFile(TEST_WORKBOOK_NAME);

		wb.getSheet("Sheet1").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

		createExcelFile(wb, TEST_WORKBOOK_NAME);
	}
	
}
